#!/usr/bin/env python
import os
from pysqlite2 import dbapi2 as sqlite

class DieuDatabase:
    """
    Class used to abstract away the sqlite database used by dieu to store
    posts and tags.
    """

    def __init__(self, username, system):
        """
        DieuDatabase is constructed as such;
        
        mydatabase = dieu.database.DieuDatabase(username, system)
        
        Where username is the username of the user, and system is the
        social bookmarking service used (at present, valid options are
        'del.icio.us' and 'ma.gnolia'). Both must be given as strings.
        """
        self.__dieu_username = username
        self.__dieu_system = system
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        
        try: self.get_post(1)
        except sqlite.OperationalError:
            self.__create_post_table()
            self.__create_tags_table()
    
    def __create_post_table(self):
        """
        Creates an sqlite table in the dieu database for posts. Run
        by the class init if needed.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        self.__dieu_database_cursor.execute('CREATE TABLE Bookmarks (id INTEGER PRIMARY KEY, Title VARCHAR(50), URL VARCHAR(50), Tags VARCHAR(50), Extra VARCHAR(50))')
        self.__dieu_database.commit()
    
    def __create_tags_table(self):
        """
        Works just as create_post_table, except that it creates a table
        for tags. Run by default by the class init, so you don't need to
        try it.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        self.__dieu_database_cursor.execute('CREATE TABLE Tags (id INTEGER PRIMARY KEY, Tag VARCHAR(50))')
        self.__dieu_database.commit()
    
    def add_post(self, Title, URL, Tags, Extra):
        """
        Adds a post to the dieu database. Should not be confused with
        dieu.bookmark.add_post, which sends a post to the web-based
        social bookmarking service used by the user.
        
        All values should be in strings. The Title is used to identify
        the bookmark, and is the one displayed in the statusicon menu.
        The URL should be a working URL. Tags should be a single space-
        separated string, ie. "foo bar baz". Extra should be a brief
        description of the bookmark, preferably under 255 characters.
        
        This database is not synced with the online version, and it
        generally should not be called except when repopulating or
        updating the database.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        if '' in Tags.split(' '):
            Tags = Tags[:len(Tags)-1]
            self.__dieu_database_cursor.execute('INSERT INTO Bookmarks VALUES (null, ?, ?, ?, ?)', (Title, URL, Tags, Extra))
        else:
            self.__dieu_database_cursor.execute('INSERT INTO Bookmarks VALUES (null, ?, ?, ?, ?)', (Title, URL, Tags, Extra))
        
        self.__dieu_database.commit()
    
    def add_tag(self, Tag):
        """
        Adds a tag to the dieu database. The same caveats that apply
        to add_post apply to this as well. Each tag should be an
        individual word string, for example 'foo' or 'bar'.
        """        
        if Tag != ' ':
            if Tag != '':
                self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
                self.__dieu_database_cursor = self.__dieu_database.cursor()
                command = 'INSERT INTO Tags VALUES (null, "' + str(Tag) + '")'
                self.__dieu_database_cursor.execute(command)
                
                self.__dieu_database.commit()
    
    def get_post(self, postid):
        """
        Gets post by postid, the number given as the first item of each
        item by the get_all_* and get_recent_* methods. Should be
        an int.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        command = 'SELECT * FROM Bookmarks WHERE id=' + str(postid)
        self.__dieu_database_cursor.execute(command)
        return self.__dieu_database_cursor.fetchall()
    
    def get_tag(self, tagid):
        """
        Gets a tag by tagid, the number given as the first item of
        each item by the get_all_* and get_recent_* methods. Should
        be an int.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        command = 'SELECT * FROM Bookmarks WHERE id=' + str(tagid)
        self.__dieu_database_cursor.execute(command)
        return self.__dieu_database_cursor.fetchall()
    
    def get_all_posts(self, sortby):
        """
        Returns all posts as a list of tuples. sortby determines the
        order in which they should be returned; 'Default' to get them
        by the order in which they were put in, 'Title' to be
        alphabetical by title, 'URL' to be alphabetical by URL, and
        'Tag' and 'Extra' to similar effect.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        if sortby == 'Default':
            self.__dieu_database_cursor.execute('SELECT * FROM Bookmarks')
            __posts = self.__dieu_database_cursor.fetchall()
            __posts.reverse()
            return __posts
        else:
            self.__dieu_database_cursor.execute('SELECT * FROM Bookmarks ORDER BY ?', (sortby))
            __posts = self.__dieu_database_cursor.fetchall()
            __posts.reverse()
            return __posts
    
    def get_all_tags(self, sortby):
        """
        Returns all tags as a list of tuples. sortby determines
        the order in which they should be returned; 'Default' to get
        them by the order in which they were put in, or 'Alphabetical'
        to get them in alphabetical order.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        if sortby == 'Alphabetical':
            self.__dieu_database_cursor.execute('SELECT * FROM Tags ORDER BY Tag')
            __tags = self.__dieu_database_cursor.fetchall()
            return __tags
        elif sortby == 'Default':
            self.__dieu_database_cursor.execute('SELECT * FROM Tags')
            __tags = self.__dieu_database_cursor.fetchall()
            return __tags
    
    def get_recent_posts(self, sortby, num):
        """
        Identical to get_all_posts, with the exception of num, an int
        which determines the number of posts that should be returned.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        if sortby == 'Default':
            self.__dieu_database_cursor.execute('SELECT * FROM Bookmarks')
            __posts = self.__dieu_database_cursor.fetchall()
            __posts.reverse()
            __recent_posts = []
            for post in __posts:
                if len(__recent_posts) != num:
                    __recent_posts.append(post)
            return __recent_posts
        else:
            self.__dieu_database_cursor.execute('SELECT * FROM Bookmarks ORDER BY Tags')
            __posts = self.__dieu_database_cursor.fetchall()
            __posts.reverse()
            __recent_posts = []
            for post in __posts:
                if len(__recent_posts) != num:
                    __recent_posts.append(post)
            return __recent_posts
    
    def get_recent_tags(self, sortby, num):
        """
        Identical to get_all_tags, with the exception of num, an int
        which determines the number of tags that should be returned.
        """
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        if sortby == 'Alphabetical':
            self.__dieu_database_cursor.execute('SELECT * FROM Tags ORDER BY Tag')
            __tags = self.__dieu_database_cursor.fetchall()
            __recent_tags = []
            for tag in __tags:
                if len(__recent_tagss) != num:
                    __recent_tags.append(tag)
            return __recent_tags
        elif sortby == 'Default':
            self.__dieu_database_cursor.execute('SELECT * FROM Tags')
            __tags = self.__dieu_database_cursor.fetchall()
            __recent_tags = []
            for tag in __tags:
                if len(__recent_tags) != num:
                    __recent_tags.append(tag)
            return __recent_tags
    
    def search(self, arg):
        self.__dieu_database = sqlite.connect(os.environ['HOME'] + '/.config/dieu/' + self.__dieu_username + '_' + self.__dieu_system + '.db')
        self.__dieu_database_cursor = self.__dieu_database.cursor()
        
        db = self.get_all_posts('Default')
        
        returned = []
        
        for bookmark in db:
            if arg in bookmark[1]:
                if bookmark in returned:
                    pass
                else:
                    returned.append(bookmark)
            if arg in bookmark[2]:
                if bookmark in returned:
                    pass
                else:
                    returned.append(bookmark)
            if arg in bookmark[3]:
                if bookmark in returned:
                    pass
                else:
                    returned.append(bookmark)
            if arg in bookmark[4]:
                if bookmark in returned:
                    pass
                else:
                    returned.append(bookmark)
                    
        return returned
                
